import sqlite3 as lite
import pathlib as pl


def create_stra():
    hp = pl.Path.home()
    dbpath = hp.joinpath('.bq')
    if not pl.Path.exists(dbpath):
        print(dbpath.__str__())
        dbpath.mkdir()
    dbname = dbpath.__str__() + '/stra.sqlite'
    db = lite.connect(dbname)
    sql = '''
    CREATE TABLE strategy_sign (
    id serial NOT NULL, -- 自增序列
	tradingday varchar(8) NOT NULL, -- 交易日
	order_time varchar(20) NOT NULL, -- 信号时间:yyyy-MM-dd HH:mm:ss
	strategy_group varchar(255) NULL, -- 策略组(名)
	strategy_id varchar(32) NOT NULL, -- 策略标识
	instrument varchar(32) NOT NULL, -- 合约
	"period" int4 NOT NULL, -- 周期(单位-分钟)
	sign varchar(512) NOT NULL, -- 信号内容:json
	remark varchar(512) NULL, -- 备注
	insert_time timestamp NULL  -- DEFAULT now()入库时间
);
CREATE INDEX newtable_instrument_idx ON strategy_sign USING btree (instrument, period);
CREATE INDEX newtable_strategy_id_idx ON strategy_sign USING btree (strategy_id);
CREATE INDEX newtable_tradingday_idx ON strategy_sign USING btree (tradingday);
COMMENT ON TABLE strategy_sign IS '策略信号';

-- Column comments

COMMENT ON COLUMN strategy_sign.tradingday IS '交易日';
COMMENT ON COLUMN strategy_sign.order_time IS '信号时间:yyyy-MM-dd HH:mm:ss';
COMMENT ON COLUMN strategy_sign.instrument IS '合约';
COMMENT ON COLUMN strategy_sign."period" IS '周期(单位-分钟)';
COMMENT ON COLUMN strategy_sign.strategy_id IS '策略标识';
COMMENT ON COLUMN strategy_sign.sign IS '信号内容:json';
COMMENT ON COLUMN strategy_sign.remark IS '备注';
COMMENT ON COLUMN strategy_sign.insert_time IS '入库时间';
COMMENT ON COLUMN strategy_sign.id IS '自增序列';
COMMENT ON COLUMN strategy_sign.strategy_group IS '策略组(名)';
'''
    for s in sql.split(';'):
        db.execute(s)


if __name__ == "__main__":
    create_stra()
